package dao_service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import dao_pool.Pool;

import i_dao.I_User_Detail_Dao;
import Protocol.user.BasePersonInfo;
import Protocol.user.DetailPersonInfo;

public class UserDetailImp implements I_User_Detail_Dao {
	Pool        m_Pool = Pool.getInstance(); 
	@Override
	public boolean insertDetailInfo(DetailPersonInfo detailInfo) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		try {
			Statement statement = null;
			statement=conn.createStatement();
					int count=0;
					int num=0;
					String n_sql="SELECT user_id From detail_info";
					ResultSet rs=statement.executeQuery(n_sql);
					while(rs.next()){
						count++;
						if(rs.getInt("user_id")!=detailInfo.getUser_id()){
							num++;
						}
						}
					if(count==num){
			 SimpleDateFormat sFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					int new_user_id=detailInfo.getUser_id();
					String new_user_birthday=sFormat.format(detailInfo.getBirthday());
					String new_email=detailInfo.getE_mail();
					String new_user_tele=detailInfo.getTel();
					String new_user_description=detailInfo.getSelfDesc();
					
					
					
					String sql="insert into detail_info (user_id,user_birthday,user_email,user_tele,user_description) values (?,?,?,?,?)";
					
					PreparedStatement prstmt=conn.prepareStatement(sql);
					
					prstmt.setLong(1, new_user_id);
					prstmt.setString(2,new_user_birthday);
					prstmt.setString(3,new_email);
					prstmt.setString(4,new_user_tele);
					prstmt.setString(5,new_user_description);
					
					 prstmt.executeUpdate();
					 
				  
					return true;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
					m_Pool.freeConnection(conn);
				}
				
				return false;
		
	}

	@Override
	public boolean updateDetailInfo(DetailPersonInfo detailInfo) {
		// TODO Auto-generated method stub
		  Connection conn=m_Pool.getConnection();	
			
			try {
				Statement statement = null;
				statement=conn.createStatement();
						int count=0;
						int num=0;
						String n_sql="SELECT user_id From detail_info";
						ResultSet rs=statement.executeQuery(n_sql);
						while(rs.next()){
							count++;
							if(rs.getInt("user_id")!=detailInfo.getUser_id()){
								num++;
							}
							}
						if(count==num+1){
				SimpleDateFormat sFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				int new_user_id=detailInfo.getUser_id();
				String new_user_birthday=sFormat.format(detailInfo.getBirthday());
				String new_email=detailInfo.getE_mail();
				String new_user_tele=detailInfo.getTel();
				String new_user_description=detailInfo.getSelfDesc();
				
				String sql="update detail_info set user_id = ?,user_birthday=?,user_email=?,user_tele=?,user_description=? where user_id=?";
				PreparedStatement prstmt=conn.prepareStatement(sql);
				
				prstmt.setLong(1, new_user_id);
				prstmt.setString(2,new_user_birthday);
				prstmt.setString(3,new_email);
				prstmt.setString(4,new_user_tele);
				prstmt.setString(5,new_user_description);
			
				prstmt.setLong(6,new_user_id);
				 prstmt.executeUpdate();
				 
			 
				return true;
						}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				m_Pool.freeConnection(conn);
			}
			
			return false;
	}

	@Override
	public boolean deleteDetailInfo(int user_id) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			
			int count=0;
			int num=0;
			statement=conn.createStatement();
			String n_sql="SELECT user_id From detail_info";
			ResultSet rs=statement.executeQuery(n_sql);
			while(rs.next()){
				count++;
				if(rs.getInt("user_id")!=user_id){
					num++;
				}
				
			}
			if(count==num+1){
			statement=conn.createStatement();
			String sql="DELETE from detail_info where user_id='"+user_id+"'";
			statement.execute(sql);
			return true;
			}
			m_Pool.freeConnection(conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public DetailPersonInfo getDetailInfoById(int user_id) {
		// TODO Auto-generated method stub
		DetailPersonInfo dpi=null;
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="select*from detail_info where user_id='"+user_id+"'";
			ResultSet rs = statement.executeQuery(sql);
			
			if(rs.next()) {
				dpi = new DetailPersonInfo();
				dpi.setUser_id(rs.getInt("user_id"));
				dpi.setBirthday(rs.getDate("user_birthday"));
				dpi.setE_mail(rs.getString("user_email"));
				dpi.setTel(rs.getString("user_tele"));
				dpi.setSelfDesc(rs.getString("user_description"));
				rs.close();
				m_Pool.freeConnection(conn);
				return dpi;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public ArrayList<Integer> getSimilardexcUserId(String self_desc) {
		// TODO Auto-generated method stub
		ArrayList<Integer> arr=new ArrayList<Integer>();
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="select user_id from detail_info where user_description like'%"+self_desc+"%'";
			
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next())
			arr.add(rs.getInt("user_id"));
			m_Pool.freeConnection(conn);
			  return arr;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}

	@Override
	public boolean deleteDetaiInfos(ArrayList<Integer> userIdList) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			for(int i=0;i<userIdList.size();i++){
				int count=0;
				int num=0;
				statement=conn.createStatement();
				String n_sql="SELECT user_id From detail_info";
				ResultSet rs=statement.executeQuery(n_sql);
				while(rs.next()){
					count++;
					if(rs.getInt("user_id")!=userIdList.get(i)){
						num++;
					}
					
				}
				if(count==num+1){
			statement=conn.createStatement();
			String sql="DELETE from detail_info where user_id='"+userIdList.get(i)+"'";
			statement.execute(sql);
			}
				return true;
			}
			m_Pool.freeConnection(conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
		
	}

}
